package io.gitee.macxiang.utils;

import java.util.ArrayList;
import java.util.List;

import com.alibaba.fastjson2.JSON;
import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import com.alibaba.fastjson2.JSONWriter;

import io.gitee.macxiang.entity.Rule;
import io.gitee.macxiang.entity.SqlFieldMap;
import io.gitee.macxiang.sql.wapper.BaseService;
import io.gitee.macxiang.sql.wapper.BaseSet;
import io.gitee.macxiang.sql.wapper.BaseWhere;
import io.gitee.macxiang.sql.wapper.WhereResult;

public class SQL extends Times {
  public static String safeSql(String sql, String key) {
    if (sql != null) {
      String ret = "";
      byte bs[] = sql.getBytes(), b = 0;
      boolean lb = true, replace = key != null;
      int i = 0, len = bs.length, last = 0;
      while (i < len) {
        if (bs[i] != '\\') { // 排除转义符
          if (lb) { // 判断引号外 (非字符串内容)
            if (bs[i] == '#') {
              if (replace) {
                ret += sql.substring(last, i) + key;
                last = i + 1;
              }
            } else if (bs[i] == '\'' || bs[i] == '"' || bs[i] == '`') { // 找到起始符号 || bs[i] == '`' 此规则容易导致全线超限 不过查询无所谓了
              b = bs[i]; // 设定符号值
              lb = false;
            } else if ((bs[i] >= 'A' && bs[i] <= 'Z') || (bs[i] >= 'a' && bs[i] <= 'z')) { // 有字母
              return null;
            }
          } else if (bs[i] == b) { // 寻找结束符 此时,i 一定是大于0
            lb = true; // 本次字符串完毕
            b = 0; // 留作循环结束后判定符号是否匹配
          }
        } else { // 再加1;
          i++;
        }
        i++;
      }
      if (b == 0) {
        return ret + sql.substring(last);
      }
    }
    return null;
  }

  public static String safeString(Object param) {
    return safeString(param, false);
  }

  private static String bytesToHex(byte[] bytes) {
    if (bytes == null) {
      return null;
    }
    StringBuilder hexString = new StringBuilder(2 * bytes.length);
    for (byte b : bytes) {
      String hex = Integer.toHexString(0xff & b);
      if (hex.length() == 1) {
        hexString.append('0');
      }
      hexString.append(hex);
    }
    return "0x" + hexString.toString();
  }

  public static String safeString(Object param, boolean quote) {
    String r = "";
    if (param != null) {
      if (param instanceof String) {
        r = (String) param;
      } else {
        Class<?> c = param.getClass();
        if (c.isArray()) {
          c = c.getComponentType();
          if (Byte.class.isAssignableFrom(c) || byte.class == c) {
            byte[] bytes = null;
            if (byte.class == c) {
              bytes = (byte[]) param;
            } else {
              Byte[] t = (Byte[]) param;
              bytes = new byte[t.length];
              for (int i = 0; i < t.length; i++) {
                bytes[i] = t[i] != null ? t[i] : 0;
              }
            }
            return bytesToHex(bytes);
          }
        }
        r = JSON.toJSONString(param,
            JSONWriter.Feature.WriteBigDecimalAsPlain,
            JSONWriter.Feature.WriteByteArrayAsBase64, JSONWriter.Feature.WriteNullListAsEmpty);

      }
    }
    r = r.replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "\\\\'");

    return !quote || Datas.isNumeric(r) ? r : ("'" + r + "'");
  }

  public static String safeJSON(Object param) {
    String r = "";
    if (param != null) {
      if (param instanceof String) {
        r = (String) param;
        if (r.toLowerCase().equals("null")) {
          return "NULL";
        }
      } else {
        Class<?> c = param.getClass();
        if (c.isArray()) {
          c = c.getComponentType();
          if (Byte.class.isAssignableFrom(c) || byte.class == c) {
            byte[] bytes = null;
            if (byte.class == c) {
              bytes = (byte[]) param;
            } else {
              Byte[] t = (Byte[]) param;
              bytes = new byte[t.length];
              for (int i = 0; i < t.length; i++) {
                bytes[i] = t[i] != null ? t[i] : 0;
              }
            }
            return bytesToHex(bytes);
          }
        }
        r = JSON.toJSONString(param,
            JSONWriter.Feature.WriteBigDecimalAsPlain,
            JSONWriter.Feature.WriteByteArrayAsBase64, JSONWriter.Feature.WriteNullListAsEmpty);
      }
    }
    return JSON.toJSONString(r);
  }

  private static <W extends BaseWhere> void setRules(List<Rule> rules, W w, List<SqlFieldMap> fs) {
    for (Rule rule : rules) {
      for (SqlFieldMap f : fs) {
        if (f.field.getName().equals(rule.field)) {
          String[] sa = null;
          switch (rule.logic.trim().toLowerCase()) {
            case "=":
            case "eq":
              w.eq(f.sqlName, safeJSON(rule.value), rule.or);
              break;
            case "<>":
            case "ne":
              w.ne(f.sqlName, safeJSON(rule.value), rule.or);
              break;
            case ">":
            case "gt":
              w.gt(f.sqlName, safeJSON(rule.value), rule.or);
              break;
            case ">=":
            case "ge":
              w.ge(f.sqlName, safeJSON(rule.value), rule.or);
              break;
            case "<":
            case "lt":
              w.lt(f.sqlName, safeJSON(rule.value), rule.or);
              break;
            case "<=":
            case "le":
              w.le(f.sqlName, safeJSON(rule.value), rule.or);
              break;
            case "between":
              if (rule.value.indexOf('-') >= 0) {
                sa = rule.value.split("-");
              } else {
                sa = (String[]) JSON.parseArray(rule.value, String.class).toArray();
              }
              if (sa != null && sa.length == 2) {
                w.between(f.sqlName, safeJSON(sa[0]), safeJSON(sa[1]), rule.or);
              }
              break;
            case "not between":
            case "notbetween":
              if (rule.value.indexOf('-') >= 0) {
                sa = rule.value.split("-");
              } else {
                sa = (String[]) JSON.parseArray(rule.value, String.class).toArray();
              }
              if (sa != null && sa.length == 2) {
                w.between(f.sqlName, safeJSON(sa[0]), safeJSON(sa[1]), rule.or);
              }
              break;
            case "like":
              w.like(f.sqlName, safeJSON(rule.value), rule.or);
              break;
            case "not like":
            case "notlike":
              w.notLike(f.sqlName, safeJSON(rule.value), rule.or);
              break;
            case "is null":
            case "isnull":
              w.isNull(f.sqlName, rule.or);
              break;
            case "is not null":
            case "isnotnull":
              w.isNotNull(f.sqlName, rule.or);
              break;
            case "in":
              sa = (String[]) JSON.parseArray(rule.value, String.class).toArray();
              if (sa != null && sa.length > 0) {
                w.in(f.sqlName, sa, rule.or);
              }
              break;
            case "not in":
            case "notin":
              sa = (String[]) JSON.parseArray(rule.value, String.class).toArray();
              if (sa != null && sa.length > 0) {
                w.notIn(f.sqlName, sa, rule.or);
              }
              break;
            case "regexp":
              w.setWhere(String.format("%s REGEXP %s", f.sqlName, safeJSON(rule.value)), rule.or);
              break;
            case "<=>":
              w.setWhere(String.format("%s<=>%s", f.sqlName, safeJSON(rule.value)), rule.or);
              break;
            case "&":
            case "|":
            case "^":
            case "~":
              w.setWhere(String.format("%s%s%s", f.sqlName, rule.logic, Long.parseLong(rule.value)), rule.or);
              break;
            default:
              break;
          }
          break; // 跳出本次循环;如果前面抛出了异常,说明本语句有问题,不处理chield
        }
      }
      if (rule.children != null && rule.children.size() > 0) { // 有子条件,进行递归
        w.group(ww -> {
          setRules(rule.children, ww, fs);
        }, w, rule.childrenOr);
      }
    }
  }

  private static String fieldName2sqlName(List<SqlFieldMap> fs, String fieldName) {
    String ret = null;
    for (SqlFieldMap f : fs) {
      if (f.field.getName().equals(fieldName)) {
        ret = f.sqlName;
        break;
      }
    }
    return ret;
  }

  /**获取查询条件
   * @param <E>     当前service对应的数据表实体类
   * @param <W>     当前service对应的数据表查询类
   * @param <S>     当前service对应的数据表设置类
   * @param service 当前service; 直接使用this即可
   * @param p       前端请求的参数
   * @return        查询类
   */
  public static <E, W extends BaseWhere, S extends BaseSet> WhereResult<E, W, S> whereResult(
      BaseService<E, W, S> service, JSONObject p) {
    WhereResult<E, W, S> ret = service.where(w -> {
      if (p != null) {
        String idName = service.primary.field.getName(), s;
        if (p.containsKey(idName) && (s = p.getString(idName)).length() > 0) {
          try {
            w.in(service.primary.sqlName, JSON.parseArray(s, String.class).toArray(), false);
          } catch (Exception e) {
            w.eq(service.primary.sqlName, safeJSON(s), false);
          }
        } else if (p.containsKey("#rule")) {
          try {
            setRules(p.getJSONArray("#rule").toJavaList(Rule.class), w, service.fs);
          } catch (Exception e) {
            utils.out(e);
          }
        } else {
          List<Rule> rules = new ArrayList<>();
          service.fs.forEach(e -> {
            if (p.containsKey(e.field.getName())) {
              rules.add(new Rule(false, e.field.getName(), "=", p.getString(e.field.getName()), null));
            }
          });
          setRules(rules, w, service.fs);
        }

      }
    });
    JSONArray ja, b;
    String s;
    try { // #order
      ja = p.getJSONArray("#order");
      if (ja != null) {
        for (int i = 0, l = ja.size(); i < l; i++) {
          b = ja.getJSONArray(i);
          s = fieldName2sqlName(service.fs, b.getString(0));
          if (s != null) {
            ret.orderBy(s, b.getString(1).toUpperCase().equals("ASC"));
          }
        }
      }
    } catch (Exception e) {
    }
    try { // #group
      p.getJSONArray("#group").toJavaList(String.class).forEach(e -> {
        String t = fieldName2sqlName(service.fs, e);
        if (t != null) {
          ret.groupBy(t);
        }
      });
    } catch (Exception e) {
    }
    try { // #page
      if (p.containsKey("#page")) {
        JSONObject page = p.getJSONObject("#page");
        ret.limit(page.getIntValue("current"), page.getIntValue("size"));
      }
    } catch (Exception e) {
    }
    return ret;
  }
}
